﻿Imports System.Data.SqlClient
Public Class frmPrerequisites
    Dim CN As New SqlConnection
    Dim strCoursesID As String
    Private Sub frmPrerequisites_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        CN.ConnectionString = My.Settings.IT308ConnectionString
        Load_cboCourses()
    End Sub
    Private Sub Load_cboCourses()
        'WB - Creates the connection to the Database to load Courses into cboCourses
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "SELECT ID, Name FROM COURSES ORDER BY ID"
        Dim Reader As SqlDataReader
        Reader = Nothing
        'WB - Tries to connect to the database - if unsuccessfull it reports error and closes the window
        Try
            CN.Open()
        Catch ex As Exception
            MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
        End Try

        'WB - Reads Ids and names from the COURSES table and enters them into cboNames after clearing
        Try
            cboCourses.Items.Clear()
            Reader = cmd.ExecuteReader
            While Reader.Read
                cboCourses.Items.Add(Convert.ToString(Reader.Item("ID")).Trim & " " & Convert.ToString(Reader.Item("Name")).Trim)
            End While
        Catch ex As Exception
        Finally
            'WB - Closes connection
            Reader.Close()
            If CN.State = ConnectionState.Open Then CN.Close()
        End Try
    End Sub
    Private Sub Load_lstAllCourses()
        'WB - Creates the command to load course IDs and names into lstAllCourses
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "SELECT ID, Name FROM COURSES"
        Dim Reader As SqlDataReader

        'WB - Tries to connect to the database - if unsuccessfull it reports error and closes the window
        Try
            CN.Open()
        Catch ex As Exception
            MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
            Me.Close()
        End Try

        'WB - Reads IDs and Names from the COURSES Table and enters them into lstAllCourses after clearing
        Reader = cmd.ExecuteReader
        lstAllCourses.Items.Clear()
        While Reader.Read
            lstAllCourses.Items.Add(Convert.ToString(Reader.Item("ID")).Trim & " " & Convert.ToString(Reader.Item("Name")).Trim)
        End While

        'WB - Closes connection with the database if it is still open
        If CN.State = ConnectionState.Open Then CN.Close()

        'WB - removes the items from lstAllCourses that are already in lstPrerequisits.
        For Each Item In lstPrerequisites.Items
            lstAllCourses.Items.Remove(Item)
        Next

    End Sub
    Private Sub Load_lstPrerequisits()
        'WB - Creates the command to load courseIDs into lstPrerequisits
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "SELECT COURSES.ID, COURSES.Name From COURSES INNER JOIN PREREQS ON COURSES.ID = PREREQS.PrereqsID WHERE PREREQS.CoursesID = @CoursesID"
        Dim Reader As SqlDataReader

        'WB - Tries to connect to the database - if unsuccessfull it reports error and closes the window
        Try
            CN.Open()
        Catch ex As Exception
            MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
            Me.Close()
        End Try

        'WB - Reads IDs from the COURSES Table and enters them into lstPrerequisits after clearing
        cmd.Parameters.Add("@CoursesID", SqlDbType.VarChar, 6)
        cmd.Parameters("@CoursesID").Value = cboCourses.SelectedItem.ToString.Substring(0, cboCourses.SelectedItem.ToString.IndexOf(" "))
        Reader = cmd.ExecuteReader
        lstPrerequisites.Items.Clear()
        While Reader.Read
            lstPrerequisites.Items.Add(Convert.ToString(Reader.Item("ID")).Trim & " " & Convert.ToString(Reader.Item("Name")).Trim)
        End While

        'WB - Closes connection with the database if it is still open
        If CN.State = ConnectionState.Open Then CN.Close()
    End Sub

    Private Sub cboCourses_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboCourses.SelectedIndexChanged
        If cboCourses.SelectedIndex = -1 Then
            MessageBox.Show("Please Select a Course to Procede", "Make a Selection")
        Else
            strCoursesID = cboCourses.SelectedItem.ToString.Substring(0, cboCourses.SelectedItem.ToString.IndexOf(" "))
            Load_lstPrerequisits()
            Load_lstAllCourses()
        End If
    End Sub

    Private Sub btnAddCourse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddCourse.Click
        'WB - Verify that an item has been selected to add.
        If lstAllCourses.SelectedIndex = -1 Then
            MessageBox.Show("No Course Selected to Add", "Error")
        Else
            'WB - declare and instantiate neccesary variables to connect to Database
            Dim intRows As Integer
            Dim cmd As New SqlCommand
            cmd.Connection = CN

            'WB - Tries to connect to the database - if unsuccessfull it reports error and closes program
            Try
                CN.Open()
            Catch ex As Exception
                MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
                Me.Close()
            End Try

            'WB - Sets up the New Command to enter the Record into PROGRAMCOURSES
            Try
                cmd.CommandText = "INSERT PREREQS (CoursesID, PrereqsID) VALUES (@CoursesID, @PrereqsID)"
                cmd.Parameters.Add("@CoursesID", SqlDbType.VarChar, 6)
                cmd.Parameters.Add("@PrereqsID", SqlDbType.VarChar, 6)
                cmd.Parameters("@CoursesID").Value = strCoursesID
                cmd.Parameters("@PrereqsID").Value = lstAllCourses.SelectedItem.ToString.Substring(0, lstAllCourses.SelectedItem.ToString.IndexOf(" "))

                'WB - Performs the Execution
                intRows = cmd.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox("Error adding course to Prerequisits - " & ex.Message)
            End Try

            'WB - Reports to the user if the add statement failed
            If intRows = 0 Then
                MsgBox("Error adding course to Prerequisits")
            End If

            'WB - Closes connection with the database if it is still open
            If CN.State = ConnectionState.Open Then CN.Close()
            'WB -  refreshes the list boxes
            lstPrerequisites.Items.Add(lstAllCourses.SelectedItem)
            lstAllCourses.Items.Remove(lstAllCourses.SelectedItem)
        End If
    End Sub

    Private Sub btnRemoveCourse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRemoveCourse.Click
        'WB - Verify that an item has been selected to remove.
        If lstPrerequisites.SelectedIndex = -1 Then
            MessageBox.Show("No Course Selected to Remove", "Error")
        Else
            'WB - declare and instantiate neccesary variables to connect to Database
            Dim cmd As New SqlCommand
            cmd.Connection = CN
            Dim intRows As Integer

            'WB - Tries to connect to the database - if unsuccessfull it reports error and closes program
            Try
                CN.Open()
            Catch ex As Exception
                MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
                Me.Close()
            End Try

            'WB - Sets up the New Command to enter the Record into PROGRAMCOURSES
            Try
                cmd.CommandText = "DELETE PREREQS WHERE CoursesID = @CoursesID AND PrereqsID = @PrereqsID"
                cmd.Parameters.Add("@CoursesID", SqlDbType.VarChar, 6)
                cmd.Parameters.Add("@PrereqsID", SqlDbType.VarChar, 6)
                cmd.Parameters("@CoursesID").Value = strCoursesID
                cmd.Parameters("@PrereqsID").Value = lstPrerequisites.SelectedItem.ToString.Substring(0, lstPrerequisites.SelectedItem.ToString.IndexOf(" "))

                'WB - Performs the Execution
                intRows = cmd.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox("Error removing course from the Prerequisits - " & ex.Message)
            End Try

            'WB - Reports to the user if the add statement failed
            If intRows = 0 Then
                MsgBox("Error removing course from Program Curriculum")
            End If

            'WB - Closes connection with the database if it is still open
            If CN.State = ConnectionState.Open Then CN.Close()
            'WB -  refreshes the list boxes
            Load_lstPrerequisits()
            Load_lstAllCourses()
        End If
    End Sub
End Class